﻿using System;
using System.Collections.Generic;

using System.Text;
using Portal.Interface;
using Portal.Environment;
using Portal.Language;
using System.Data;
using System.Data.SqlClient;

namespace Portal.CMS
{
    public class DocumentBLL
    {
        public DocumentCls CreateModel(string ActiveLanguage, string DocumentId)
        {
            return CreateModel(Environment.Environments.ConnectionString, ActiveLanguage, DocumentId);
        }

        public DocumentCls CreateModel(string ConnectionString,string ActiveLanguage, string DocumentId)
        {
            string Query = "select * from TableDocument where DocumentId=@DocumentId";
            SqlConnection sqlConnection = new SqlConnection(ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("@DocumentId", SqlDbType.VarChar);
            sqlCommand.Parameters["@DocumentId"].Value = DocumentId;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            if (dtResult.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                DocumentCls DocumentCls = new DocumentCls();
                DocumentCls.DocumentId = (string)dtResult.Rows[0]["DocumentId"];
                DocumentCls.Subject = (string)dtResult.Rows[0]["Subject"];
                try
                {
                    DocumentCls.Subject = (string)dtResult.Rows[0][ActiveLanguage + "Subject"];
                }
                catch { }
              

                DocumentCls.belongSite = "";
                try
                {
                    DocumentCls.belongSite = (string)dtResult.Rows[0]["belongSite"];
                }
                catch { }

                try
                {
                    DocumentCls.Description = (string)dtResult.Rows[0][ActiveLanguage + "Description"];
                }
                catch { }
                try
                {
                    DocumentCls.Owner = (string)dtResult.Rows[0]["Owner"];
                }
                catch { }
                try
                {
                    DocumentCls.JumpUrl = (string)dtResult.Rows[0]["JumpUrl"];
                }
                catch { }

                return DocumentCls;
            }
        }

        public DocumentItemCls CreateModelItem(string ActiveLanguage, string DocumentItemId)
        {
            return CreateModelItem(Environments.ConnectionString, ActiveLanguage, DocumentItemId);
        }
        
        public DocumentItemCls CreateModelItem(string ConnectionString,string ActiveLanguage, string DocumentItemId)
        {
            string Query = "select * from TableDocumentItem where DocumentItemId=@DocumentItemId";
            SqlConnection sqlConnection = new SqlConnection(ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("@DocumentItemId", SqlDbType.VarChar);
            sqlCommand.Parameters["@DocumentItemId"].Value = DocumentItemId;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            if (dtResult.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                DocumentItemCls DocumentItemCls = new DocumentItemCls();
                DocumentItemCls.DocumentItemId = (string)dtResult.Rows[0]["DocumentItemId"];
                DocumentItemCls.DocumentId = (string)dtResult.Rows[0]["fk_Document_Id"];
                try
                {
                    DocumentItemCls.DocumentItemName = (string)dtResult.Rows[0][ActiveLanguage + "DocumentItemName"];
                }
                catch { }

                DocumentItemCls.Url = "";
                try
                {
                    DocumentItemCls.Url = (string)dtResult.Rows[0]["Url"];
                }
                catch { }

                try
                {
                    DocumentItemCls.Owner = (string)dtResult.Rows[0]["Owner"];
                }
                catch { }

                try
                {
                    DocumentItemCls.Target = (string)dtResult.Rows[0]["Target"];
                }
                catch { }
                try
                {
                    DocumentItemCls.IndexOrder = (int)dtResult.Rows[0]["IndexOrder"];
                }
                catch { }

                try
                {
                    DocumentItemCls.Description = (string)dtResult.Rows[0]["Description"];
                }
                catch { }

                try
                {
                    DocumentItemCls.ItemEntryDate = (DateTime)dtResult.Rows[0]["ItemEntryDate"];
                }
                catch { }

                try
                {
                    DocumentItemCls.FileUpload = (string)dtResult.Rows[0]["FileUpload"];
                }
                catch { }

                try
                {
                    DocumentItemCls.belongSite = (string)dtResult.Rows[0]["belongSite"];
                }
                catch { }

                try
                {
                    DocumentItemCls.IsPublishing = (int)dtResult.Rows[0]["IsPublishing"];
                }
                catch { }

                return DocumentItemCls;
            }
        }

        public DataTable Reading(string belongSite)
        {
            string Query = "select * from TableDocument where belongSite='" + belongSite + "'";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }


        public DataTable ReadingAvailableAlbums(string DocumentId)
        {
            string Query = "select * from TableAlbum where AlbumId not in (select fk_Album_Id from TableAlbumItem where fk_Document_Id='" + DocumentId + "')";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }


        public DataTable ReadingForSyncFromTool(string ConnectionString, string belongSite)
        {
            string Query = "select DocumentItemId from TableDocumentItem where IsPublishing=1 and belongSite='" + belongSite + "'";
            SqlConnection sqlConnection = new SqlConnection(ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }


        public DataTable ReadingDocumentItems(string DocumentId, string Keyword)
        {
            string Query = "select * from TableDocumentItem where fk_Document_Id='" + DocumentId + "' ";
            if (Keyword.Trim().Length > 0)
            {
                Query += " and [" + Environments.ActiveLanguage + "DocumentItemName] like N'%" + Keyword + "%'";
            }
            Query += " order by [IndexOrder]";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public string AddDocument(
            SqlTransaction sqlTransaction, 
            string belongSite, 
            string ActiveLanguage, 
            string Subject, 
            string Description,
            string JumpUrl)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string DocumentId = System.Guid.NewGuid().ToString();
            string Query = "Insert into TableDocument(DocumentId,belongSite,ShortId,Subject,[" + ActiveLanguage + "Subject],[" + Environments.ActiveLanguage + "Description], owner, JumpUrl) values (@DocumentId,@belongSite,@ShortId,@Subject,@Subject,@Description,@owner,@JumpUrl)";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();

            sqlCommand.Parameters.Add("@DocumentId", SqlDbType.VarChar);
            sqlCommand.Parameters["@DocumentId"].Value = DocumentId;

            sqlCommand.Parameters.Add("@belongSite", SqlDbType.NVarChar);
            sqlCommand.Parameters["@belongSite"].Value = belongSite;

            sqlCommand.Parameters.Add("@ShortId", SqlDbType.VarChar);
            sqlCommand.Parameters["@ShortId"].Value = DocumentId.Substring(0, 8);


            sqlCommand.Parameters.Add("@Subject", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Subject"].Value = Subject;

            sqlCommand.Parameters.Add("@Description", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Description"].Value = Description;


            sqlCommand.Parameters.Add("@owner", SqlDbType.NVarChar);
            sqlCommand.Parameters["@owner"].Value = Environment.Environments.CurrentLoginUser.LoginName;

            sqlCommand.Parameters.Add("@JumpUrl", SqlDbType.NVarChar);
            sqlCommand.Parameters["@JumpUrl"].Value = JumpUrl;

            try
            {
                sqlCommand.ExecuteNonQuery();
               
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
            return DocumentId;
        }


        public void UpdateDocument(
            SqlTransaction sqlTransaction,
            string ActiveLanguage,
            string DocumentId,
            string Subject,
            string Description,
            string JumpUrl)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string Query = "Update TableDocument set Subject=@Subject,[" + ActiveLanguage + "Subject]=@Subject,[" + ActiveLanguage + "Description]=@Description,JumpUrl=@JumpUrl where DocumentId=@DocumentId";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@DocumentId", SqlDbType.VarChar);
            sqlCommand.Parameters["@DocumentId"].Value = DocumentId;

            sqlCommand.Parameters.Add("@Subject", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Subject"].Value = Subject;

            sqlCommand.Parameters.Add("@Description", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Description"].Value = Description;

            sqlCommand.Parameters.Add("@JumpUrl", SqlDbType.NVarChar);
            sqlCommand.Parameters["@JumpUrl"].Value = JumpUrl;

            try
            {
                sqlCommand.ExecuteNonQuery();

                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }

        public void SaveDocumentItem(
           SqlTransaction sqlTransaction,
           string ActiveLanguage,
           string DocumentId,
           DocumentItemCls DocumentItemCls)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string Query = "";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();

            try
            {
                Query = " Update TableDocumentItem set DocumentItemName=@DocumentItemName,[" + ActiveLanguage + "DocumentItemName]=@DocumentItemName,Url=@Url,Target=@Target,IndexOrder=@IndexOrder,Description=@Description where DocumentItemId=@DocumentItemId";
                sqlCommand = sqlConnection.CreateCommand();
                sqlCommand.CommandText = Query;
                sqlCommand.Transaction = sqlTransaction;
                sqlCommand.Parameters.Clear();

                sqlCommand.Parameters.Add("@DocumentItemId", SqlDbType.VarChar);
                sqlCommand.Parameters["@DocumentItemId"].Value = DocumentItemCls.DocumentItemId;

                sqlCommand.Parameters.Add("@DocumentItemName", SqlDbType.NVarChar);
                sqlCommand.Parameters["@DocumentItemName"].Value = DocumentItemCls.DocumentItemName;

                sqlCommand.Parameters.Add("@Url", SqlDbType.NVarChar);
                sqlCommand.Parameters["@Url"].Value = DocumentItemCls.Url;

                sqlCommand.Parameters.Add("@Target", SqlDbType.NVarChar);
                sqlCommand.Parameters["@Target"].Value = DocumentItemCls.Target;

                sqlCommand.Parameters.Add("@IndexOrder", SqlDbType.Int);
                sqlCommand.Parameters["@IndexOrder"].Value = DocumentItemCls.IndexOrder;


                sqlCommand.Parameters.Add("@Description", SqlDbType.NVarChar);
                sqlCommand.Parameters["@Description"].Value = DocumentItemCls.Description;

                sqlCommand.ExecuteNonQuery();

                
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }


        public void UpdateDocumentItemContent(
            SqlTransaction sqlTransaction,
            string DocumentItemId,
            byte[] b,
            string FileUpload)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string Query = "";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();

            try
            {
                Query = " Update TableDocumentItem set FileUpload=@FileUpload,DocumentContent=@DocumentContent where DocumentItemId=@DocumentItemId";
                sqlCommand = sqlConnection.CreateCommand();
                sqlCommand.CommandText = Query;
                sqlCommand.Transaction = sqlTransaction;
                sqlCommand.Parameters.Clear();

                sqlCommand.Parameters.Add("@DocumentItemId", SqlDbType.VarChar);
                sqlCommand.Parameters["@DocumentItemId"].Value = DocumentItemId;

                sqlCommand.Parameters.Add("@FileUpload", SqlDbType.NVarChar);
                sqlCommand.Parameters["@FileUpload"].Value = FileUpload;

                sqlCommand.Parameters.Add("@DocumentContent", SqlDbType.Image);
                sqlCommand.Parameters["@DocumentContent"].Value = b;

                sqlCommand.ExecuteNonQuery();

                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }



        public void DeleteDocument(SqlTransaction sqlTransaction, string DocumentId)
        {
            DocumentCls oDocumentCls = new DocumentBLL().CreateModel(Environments.ActiveLanguage, DocumentId);
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }
            Environment.Permission.DeletePermission(sqlTransaction, DocumentId);
            string Query =
                    " Delete from TableDocumentItem where fk_Document_Id=@DocumentId;" +
                    " Delete from TableDocument where DocumentId=@DocumentId";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@DocumentId", SqlDbType.VarChar);
            sqlCommand.Parameters["@DocumentId"].Value = DocumentId;

            try
            {
                sqlCommand.ExecuteNonQuery();
                
                string SubjectDeleted = "";
                if (oDocumentCls != null)
                {
                    SubjectDeleted = oDocumentCls.Subject;
                }
                Environments.AddDeletedRecordForSync(sqlTransaction, "TableDocument","Nhóm văn bản", DocumentId, SubjectDeleted);
                
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }

        public void DeleteDocumentItem(SqlTransaction sqlTransaction, string DocumentItemId)
        {
            DocumentItemCls oDocumentItemCls = new DocumentBLL().CreateModelItem(Environments.ActiveLanguage, DocumentItemId);
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }
            string Query =
                    " Delete from TableDocumentItem where DocumentItemId=@DocumentItemId;";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@DocumentItemId", SqlDbType.VarChar);
            sqlCommand.Parameters["@DocumentItemId"].Value = DocumentItemId;

            try
            {
                sqlCommand.ExecuteNonQuery();
                string SubjectDeleted = "";
                if (oDocumentItemCls != null)
                {
                    SubjectDeleted = oDocumentItemCls.DocumentItemName;
                }
                Environments.AddDeletedRecordForSync(sqlTransaction, "TableDocumentItem", "Văn bản",DocumentItemId, SubjectDeleted);
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }



        public void AddDocumentItem(
            SqlTransaction sqlTransaction,
            string belongSite,
            string DocumentId,
            string UploadFile,
            string DocumentItemName,
            string Url,
            string Description,
            byte[] b)
        {
            string ln = new System.IO.FileInfo(UploadFile).Name;
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string UploadFileName = "";
            if (ln.Trim().Length > 0)
            {
                UploadFileName = new System.IO.FileInfo(UploadFile).Name;
            }
            string DocumentItemId = System.Guid.NewGuid().ToString();
            string Query = " Insert into TableDocumentItem(DocumentItemId,belongSite,fk_Document_Id,FileUpload,DocumentItemName,[" + Environments.ActiveLanguage + "DocumentItemName], Url,Target,IndexOrder,ItemEntryDate,Description,DocumentContent,Owner, DocumentContent) values (@DocumentItemId,@belongSite,@fk_Document_Id,@FileUpload,@DocumentItemName,@DocumentItemName, @Url,@Target,@IndexOrder,GetDate(),@Description,@DocumentContent,@Owner,@DocumentContent)";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();

            sqlCommand.Parameters.Add("@DocumentItemId", SqlDbType.VarChar);
            sqlCommand.Parameters["@DocumentItemId"].Value = DocumentItemId;

            sqlCommand.Parameters.Add("@belongSite", SqlDbType.NVarChar);
            sqlCommand.Parameters["@belongSite"].Value = belongSite;


            sqlCommand.Parameters.Add("@FileUpload", SqlDbType.NVarChar);
            sqlCommand.Parameters["@FileUpload"].Value = ln;


            sqlCommand.Parameters.Add("@DocumentItemName", SqlDbType.NVarChar);
            sqlCommand.Parameters["@DocumentItemName"].Value = DocumentItemName;


            sqlCommand.Parameters.Add("@fk_Document_Id", SqlDbType.VarChar);
            sqlCommand.Parameters["@fk_Document_Id"].Value = DocumentId;

            sqlCommand.Parameters.Add("@Url", SqlDbType.VarChar);
            sqlCommand.Parameters["@Url"].Value = Url;

            sqlCommand.Parameters.Add("@Description", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Description"].Value = Description;

            sqlCommand.Parameters.Add("@Target", SqlDbType.VarChar);
            sqlCommand.Parameters["@Target"].Value = "_blank";

            sqlCommand.Parameters.Add("@IndexOrder", SqlDbType.Int);
            sqlCommand.Parameters["@IndexOrder"].Value = 0;

            sqlCommand.Parameters.Add("@DocumentContent", SqlDbType.Image);
            sqlCommand.Parameters["@DocumentContent"].Value = b;

            sqlCommand.Parameters.Add("@Owner", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Owner"].Value = Environments.CurrentLoginUser.LoginName;

            sqlCommand.Parameters.Add("@DocumentContent", SqlDbType.Image);
            if (b != null)
            {
                sqlCommand.Parameters["@DocumentContent"].Value = b;
            }
            else
            {
                sqlCommand.Parameters["@DocumentContent"].Value = DBNull.Value;
            }
            try
            {
                sqlCommand.ExecuteNonQuery();
                
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }


        public void Publishing(SqlTransaction sqlTransaction, string DocumentItemId)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string Query = " Update TableDocumentItem set IsPublishing=1 where DocumentItemId=@DocumentItemId";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@DocumentItemId", SqlDbType.VarChar);
            sqlCommand.Parameters["@DocumentItemId"].Value = DocumentItemId;


            try
            {
                sqlCommand.ExecuteNonQuery();
                
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }


        public void UnPublishing(SqlTransaction sqlTransaction, string DocumentItemId)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string Query = " Update TableDocumentItem set IsPublishing=0 where DocumentItemId=@DocumentItemId";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@DocumentItemId", SqlDbType.VarChar);
            sqlCommand.Parameters["@DocumentItemId"].Value = DocumentItemId;

            try
            {
                sqlCommand.ExecuteNonQuery();
                
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }

        public DataTable ReadingForSync(string DocumentId)
        {
            string Query = "select DocumentItemId from TableDocumentItem where fk_Document_Id=@DocumentId";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("@DocumentId", SqlDbType.VarChar);
            sqlCommand.Parameters["@DocumentId"].Value = DocumentId;
            
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public void SyncDocument(
          string DocumentId)
        {
            DataTable dtItem = ReadingForSync(DocumentId);
            for (int iIndex = 0; iIndex < dtItem.Rows.Count; iIndex++)
            {
                string DocumentItemId = (string)dtItem.Rows[iIndex]["DocumentItemId"];
                SyncDocumentItem(DocumentItemId);
            }
        }

        public void SyncDocumentItem(
            string ConnectionString,
            string DocumentItemId,
            bool   UseWsForDeploy,
            string ActiveLanguage,
            string PathRoot,
            string WorkingPath,
            string PortalAlias,
            string WsUrl,
            string WsSecurityCode)
        {
            if (UseWsForDeploy)
            {
                DocumentItemCls DocumentItemCls = new DocumentBLL().CreateModelItem(ConnectionString, ActiveLanguage, DocumentItemId);
                DocumentCls DocumentCls = new DocumentBLL().CreateModel(ConnectionString,ActiveLanguage, DocumentItemCls.DocumentId);
                DataSet ds = new DataSet();

                string Query =
                    " select * from TableDocument where DocumentId in (select fk_Document_Id from TableDocumentItem where DocumentItemId=@DocumentItemId)" +
                    " select * from TableDocumentItem where DocumentItemId=@DocumentItemId";
                SqlConnection sqlConnectionCheck = new SqlConnection(ConnectionString);
                sqlConnectionCheck.Open();
                SqlCommand sqlCommandCheck = sqlConnectionCheck.CreateCommand();
                sqlCommandCheck.CommandText = Query;
                sqlCommandCheck.Parameters.Clear();
                sqlCommandCheck.Parameters.Add("@DocumentItemId", SqlDbType.VarChar);
                sqlCommandCheck.Parameters["@DocumentItemId"].Value = DocumentItemId;

                SqlDataAdapter sqlda = new SqlDataAdapter(sqlCommandCheck);
                sqlda.Fill(ds);

                ds.Tables[0].TableName = "TableDocument";
                ds.Tables[1].TableName = "TableDocumentItem";

                string XmlData = ds.GetXml();
                Framework.AppWs.WsReturnParamCls oWsReturnParamCls = Environments.CreateAppWs(WsUrl).SyncData(
                    WsSecurityCode,
                    DocumentItemId,
                    DocumentCls.belongSite,
                    XmlData,
                    null,
                    null,
                    null,
                    Framework.AppWs.ESycDataType.DocumentItem);
                if (oWsReturnParamCls.Error) throw new Exception("WS: " + oWsReturnParamCls.ErrorMsg);
            }
        }

        /// <summary>
        /// Sync nhóm tin lên máy chủ (GroupNewsItem)
        /// </summary>
        /// <param name="GroupNewsItemId"></param>
        public void SyncDocumentItem(
            string DocumentItemId)
        {
            
        }
    }
}
